﻿Imports VN.INS.DL.DH
Imports VN.INS.BL.BE
Public Class PPERDA

    Public Function ListaPPER(ByVal strPersona As String) As DataTable
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String
        Try
            lxc_SQL = "SELECT DISTINCT PP.PPER,DESCRIPCION,PP.ABREVIATURA FROM PPER PP INNER JOIN PERSONA PE ON PE.PPER = PP.PPER "
            If strPersona <> "" Then
                lxc_SQL = lxc_SQL & " WHERE '" & strPersona & "' IN (PE.PERSONA,PE.JEFE_INMEDIATO)"
            End If
            lxc_SQL = lxc_SQL & " ORDER BY PP.PPER "
            objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return objDt
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    Public Function ListaPeriodo(ByVal strTipo As String, ByVal srtCodAnhio As String) As DataTable
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String
        Try
            lxc_SQL = "SELECT SUBSTR(PERIODO,1,6) PERIODO, DESCRIPCION FROM PERIODO WHERE TIPO_PERIODO = '" & strTipo & "' AND ANHIO = '" & srtCodAnhio & "' AND ESTADO = '1' ORDER BY PERIODO"
            objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return objDt
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    Public Function ListaTrabajos(ByVal strJefe As String, ByVal srtCodAnhio As String) As DataTable
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String
        Try
            lxc_SQL = "SELECT DISTINCT P.NOMBRES || ' ' || P.APELLIDO_PATERNO || ' ' || P.APELLIDO_MATERNO as MONITOREADOR , P.PERSONA, PP.PPER PPER_ID, " & _
                  "OZ.DESCRIPCION, PP.DESCRIPCION PPER, PD.ANHIO , pd.DESCRIPCION MES, ptp.ID_PLAN_TRABAJO_PERSONA,PTP.ESTADO AS ID_ESTADO, " & _
                  "P1.NOMBRES || ' ' || P1.APELLIDO_PATERNO || ' ' || P1.APELLIDO_MATERNO as JEFE,pd.PERIODO, E.DESCRIPCION AS ESTADO, PTP.FECHA, PTP.FECHA_APROBACION_JEFE " & _
                  "FROM PLAN_TRABAJO_PERSONA ptp inner join PERSONA P on PTP.PERSONA_MONITOREO = P.PERSONA " & _
                  "inner join periodo pd on pd.periodo = ptp.periodo  " & _
                  "inner join (SELECT DISTINCT ID_PLAN_TRABAJO_PERSONA FROM PLAN_TRABAJO_POA) PL ON PL.ID_PLAN_TRABAJO_PERSONA = PTP.ID_PLAN_TRABAJO_PERSONA " & _
                  "LEFT join OFICINA_ZONAL OZ ON OZ.OFICINA_ZONAL = P.OFICINA_ZONAL " & _
                  "inner join pper pp on PP.PPER = P.PPER " & _
                  "INNER JOIN GENERAL G ON G.ID= 'MES' AND G.CODIGO = PD.MES " & _
                  "INNER JOIN GENERAL E ON E.ID= 'ESTADO' AND E.CODIGO = PTP.ESTADO " & _
                  "inner join persona P1 on PTP.JEFE_INMEDIATO = P1.persona " & _
                  "LEFT JOIN (SELECT DISTINCT PERSONA_MONITOREO,PERSONA_ESPECIALISTA FROM POA WHERE '" & strJefe & "' IN (PERSONA_MONITOREO,PERSONA_ESPECIALISTA) ) O ON O.PERSONA_MONITOREO = P.PERSONA " & _
                  " WHERE '" & strJefe & "' IN (PTP.JEFE_INMEDIATO, PTP.PERSONA_MONITOREO,O.PERSONA_ESPECIALISTA) AND PD.PERIODO LIKE '" & srtCodAnhio & "%' ORDER BY PD.PERIODO DESC "
            objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return objDt

        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Function ListaTrabajos(ByVal strJefe As String, ByVal strMonitoreador As String, ByVal strPPER As String, ByVal strPeriodo As String, ByVal ano As String) As DataTable
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String

        Try
            lxc_SQL = "SELECT P.NOMBRES || ' ' || P.APELLIDO_PATERNO || ' ' || P.APELLIDO_MATERNO as MONITOREADOR, P.PERSONA, PP.PPER PPER_ID, " & _
                "OZ.DESCRIPCION, PP.DESCRIPCION PPER, PD.ANHIO , pd.DESCRIPCION MES, ptp.ID_PLAN_TRABAJO_PERSONA, PTP.ESTADO AS ID_ESTADO, " & _
                "P1.NOMBRES || ' ' || P1.APELLIDO_PATERNO || ' ' || P1.APELLIDO_MATERNO as JEFE,pd.PERIODO, E.DESCRIPCION AS ESTADO, PTP.FECHA, PTP.FECHA_APROBACION_JEFE " & _
                "FROM PLAN_TRABAJO_PERSONA ptp inner join PERSONA P on PTP.PERSONA_MONITOREO = P.PERSONA " & _
                "inner join periodo pd on pd.periodo = ptp.periodo  " & _
                "inner join (SELECT DISTINCT ID_PLAN_TRABAJO_PERSONA FROM PLAN_TRABAJO_POA) PL ON PL.ID_PLAN_TRABAJO_PERSONA = PTP.ID_PLAN_TRABAJO_PERSONA " & _
                "LEFT join OFICINA_ZONAL OZ ON OZ.OFICINA_ZONAL = P.OFICINA_ZONAL " & _
                "inner join pper pp on PP.PPER = P.PPER " & _
                "INNER JOIN GENERAL G ON G.ID= 'MES' AND G.CODIGO = PD.MES " & _
                "INNER JOIN GENERAL E ON E.ID= 'ESTADO' AND E.CODIGO = PTP.ESTADO " & _
                "inner join persona P1 on PTP.JEFE_INMEDIATO = P1.persona " & _
                " WHERE '" & strJefe & "' IN (PTP.JEFE_INMEDIATO, PTP.PERSONA_MONITOREO,(SELECT DISTINCT PERSONA_ESPECIALISTA FROM POA WHERE '" & strJefe & "' IN (PERSONA_MONITOREO, PERSONA_ESPECIALISTA))) "
            If strMonitoreador <> "" Then
                lxc_SQL = lxc_SQL & "AND P.PERSONA = '" & strMonitoreador & "' "
            End If
            If strPPER <> "" Then
                lxc_SQL = lxc_SQL & "AND PP.PPER = '" & strPPER & "' "
            End If
            If strPeriodo <> "all" Then
                lxc_SQL = lxc_SQL & "AND PD.PERIODO = '" & strPeriodo & "'  "
            End If
            lxc_SQL = lxc_SQL & " AND PD.ANHIO = '" & ano & "' Order by ptp.fecha "
            objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return objDt

        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Function ListaTrabajosEsp(ByVal strCodEsp As String, ByVal srtCodMonit As String, ByVal srtCodPper As String, ByVal srtCodPeriodo As String, ByVal srtCodAnhio As String) As DataTable
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String

        Try
            'By Tigrito: Commented 29/05/2013
            'lxc_SQL = "SELECT P.NOMBRES || ' ' || P.APELLIDO_PATERNO || ' ' || P.APELLIDO_MATERNO as MONITOREADOR, P.PERSONA, PP.PPER PPER_ID, " & _
            '    "OZ.DESCRIPCION, PP.DESCRIPCION PPER, PD.ANHIO , G.DESCRIPCION MES, ptp.ID_PLAN_TRABAJO_PERSONA, PTP.ESTADO AS ID_ESTADO, " & _
            '    "P1.NOMBRES || ' ' || P1.APELLIDO_PATERNO || ' ' || P1.APELLIDO_MATERNO as JEFE,pd.PERIODO, E.DESCRIPCION AS ESTADO, PTP.FECHA, PTP.FECHA_APROBACION_JEFE " & _
            '    "FROM PLAN_TRABAJO_PERSONA ptp inner join PERSONA P on PTP.PERSONA_MONITOREO = P.PERSONA " & _
            '    "inner join periodo pd on pd.periodo = ptp.periodo  " & _
            '    "inner join (SELECT DISTINCT ID_PLAN_TRABAJO_PERSONA FROM PLAN_TRABAJO_POA) PL ON PL.ID_PLAN_TRABAJO_PERSONA = PTP.ID_PLAN_TRABAJO_PERSONA " & _
            '    "LEFT join OFICINA_ZONAL OZ ON OZ.OFICINA_ZONAL = P.OFICINA_ZONAL " & _
            '    "inner join pper pp on PP.PPER = P.PPER " & _
            '    "INNER JOIN GENERAL G ON G.ID= 'MES' AND G.CODIGO = PD.MES " & _
            '    "INNER JOIN GENERAL E ON E.ID= 'ESTADO' AND E.CODIGO = PTP.ESTADO " & _
            '    "inner join persona P1 on PTP.JEFE_INMEDIATO = P1.persona " & _
            '    " WHERE '" & strCodEsp & "' IN ((SELECT DISTINCT PERSONA_ESPECIALISTA FROM POA WHERE '" & strCodEsp & "' IN (PERSONA_ESPECIALISTA))) "
            'If srtCodMonit <> "" Then
            '    lxc_SQL = lxc_SQL & "AND P.PERSONA = '" & srtCodMonit & "' "
            'End If
            'If srtCodPper <> "" Then
            '    lxc_SQL = lxc_SQL & "AND PP.PPER = '" & srtCodPper & "' "
            'End If
            'If srtCodPeriodo <> "all" Then
            '    lxc_SQL = lxc_SQL & "AND PD.PERIODO = '" & srtCodPeriodo & "'  "
            'End If
            'lxc_SQL = lxc_SQL & " AND PD.ANHIO = '" & srtCodAnhio & "' Order by ptp.fecha "

            Dim Aux1 As String = If(srtCodMonit = "", "%", "%" & srtCodMonit & "%") 'By Tigrito (29/05/2013): Para el código de Monitoreador
            Dim Aux2 As String = If(srtCodPeriodo = "all", "" & srtCodAnhio & "%", "" & srtCodPeriodo & "") 'By Tigrito (29/05/2013): Para el código de Código del periodo

            lxc_SQL = "SELECT MONI.MONITOREADOR, MONI.PERSONA, MONESP.PPER_ID, MONI.DESCRIPCION, MONESP.PPER, " & _
                                    "MONESP.ANHIO, PERIODO.DESCRIPCION MES, PTP.ID_PLAN_TRABAJO_PERSONA, PTP.ESTADO ID_ESTADO, JEFEIM.JEFE, PTP.PERIODO, ESTADO.DESCRIPCION ESTADO, PTP.FECHA, PTP.FECHA_APROBACION_JEFE " & _
                        "FROM PLAN_TRABAJO_PERSONA PTP, PERIODO, " & _
                            "(SELECT CODIGO, DESCRIPCION FROM GENERAL WHERE ID = 'MES') MES, " & _
                            "(SELECT CODIGO, DESCRIPCION FROM GENERAL WHERE ID = 'ESTADO') ESTADO, " & _
                            "(SELECT P.PERSONA, P.NOMBRES || ' ' || P.APELLIDO_PATERNO || ' ' || P.APELLIDO_MATERNO as JEFE FROM PERSONA P) JEFEIM, " & _
                            "( " & _
                            "SELECT P.PERSONA_MONITOREO, P.PPER PPER_ID,  P.ANHIO_FIRMA_POA ANHIO,  PPER.DESCRIPCION PPER " & _
                            "FROM POA P, PERSONA PERS, PPER  " & _
                            "WHERE P.PERSONA_ESPECIALISTA = PERS.PERSONA AND " & _
                                        "P.ANHIO_FIRMA_POA = '" & srtCodAnhio & "' AND " & _
                                        "P.PERSONA_ESPECIALISTA = '" & strCodEsp & "' AND " & _
                                        "P.PERSONA_MONITOREO LIKE  '" & Aux1 & "' AND " & _
                                        "P.PPER = PPER.PPER " & _
                            "GROUP BY  P.PERSONA_MONITOREO, P.PPER, PPER.DESCRIPCION, P.ANHIO_FIRMA_POA " & _
                            ") MONESP, " & _
                            "( " & _
                            "SELECT P.PERSONA, P.NOMBRES || ' ' || P.APELLIDO_PATERNO || ' ' || P.APELLIDO_MATERNO as MONITOREADOR, OZ.DESCRIPCION " & _
                            "FROM PERSONA P, OFICINA_ZONAL OZ " & _
                            "WHERE P.OFICINA_ZONAL = OZ.OFICINA_ZONAL " & _
                            ") MONI  " & _
                        "WHERE PTP.PERSONA_MONITOREO = MONESP.PERSONA_MONITOREO AND " & _
                                   "PTP.PERSONA_MONITOREO = MONI.PERSONA AND " & _
                                   "PTP.PERIODO =  PERIODO.PERIODO AND " & _
                                   "PTP.PERIODO LIKE '" & Aux2 & "' AND " & _
                                   "PERIODO.MES = MES.CODIGO AND " & _
                                   "PTP.ESTADO = ESTADO.CODIGO AND " & _
                                   "PTP.JEFE_INMEDIATO = JEFEIM.PERSONA " & _
                        "ORDER BY PERIODO DESC, MONITOREADOR ASC"
            objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")

            Return objDt

        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Function ListaTabajosAdministrador(ByVal strMonitoreador As String, ByVal strPPER As String, ByVal strPeriodo As String, ByVal srtCodAnhio As String) As DataTable
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String

        Try
            lxc_SQL = "SELECT P.NOMBRES || ' ' || P.APELLIDO_PATERNO || ' ' || P.APELLIDO_MATERNO as MONITOREADOR, P.PERSONA, PP.PPER PPER_ID, " & _
                        "OZ.DESCRIPCION, PP.DESCRIPCION PPER, PD.ANHIO , PD.DESCRIPCION MES, ptp.ID_PLAN_TRABAJO_PERSONA, PTP.ESTADO AS ID_ESTADO, " & _
                        "P1.NOMBRES || ' ' || P1.APELLIDO_PATERNO || ' ' || P1.APELLIDO_MATERNO as JEFE,pd.PERIODO, E.DESCRIPCION AS ESTADO, PTP.FECHA_APROBACION_JEFE " & _
                        "FROM PLAN_TRABAJO_PERSONA ptp INNER JOIN PERSONA P on PTP.PERSONA_MONITOREO = P.PERSONA " & _
                            "INNER JOIN periodo pd on pd.periodo = ptp.periodo  " & _
                            "INNER JOIN (SELECT DISTINCT ID_PLAN_TRABAJO_PERSONA FROM PLAN_TRABAJO_POA) PL ON PL.ID_PLAN_TRABAJO_PERSONA = PTP.ID_PLAN_TRABAJO_PERSONA " & _
                            "INNER JOIN OFICINA_ZONAL OZ ON OZ.OFICINA_ZONAL = P.OFICINA_ZONAL " & _
                            "INNER JOIN pper pp on PP.PPER = P.PPER " & _
                            "INNER JOIN GENERAL G ON G.ID= 'MES' AND G.CODIGO = PD.MES " & _
                            "INNER JOIN GENERAL E ON E.ID= 'ESTADO' AND E.CODIGO = PTP.ESTADO " & _
                            "INNER JOIN PERSONA P1 ON PTP.JEFE_INMEDIATO = P1.PERSONA "
            If strMonitoreador <> "" Then
                lxc_SQL = lxc_SQL & "AND P.PERSONA = '" & strMonitoreador & "' "
            End If
            If strPPER <> "" Then
                lxc_SQL = lxc_SQL & "AND PP.PPER = '" & strPPER & "' "
            End If
            If strPeriodo <> "all" Then
                lxc_SQL = lxc_SQL & "AND PD.PERIODO LIKE '" & strPeriodo & "'  "
            Else
                lxc_SQL = lxc_SQL & "AND PD.PERIODO LIKE '" & srtCodAnhio & "%'  ORDER BY PD.PERIODO DESC, MONITOREADOR ASC "
            End If

            '0lxc_SQL = lxc_SQL & " AND PD.ANHIO = '" & srtCodAnhio & "' ORDER BY PD.PERIODO DESC, MONITOREADOR ASC "

            objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return objDt

        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Function Lista_Planes_Trabajo_Estado(ByVal strPeriodo As String, ByVal srtCodAnhio As String) As DataTable
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String
        Try

            lxc_SQL = "SELECT ABREVIATURA PPER, PTP.PERSONA_MONITOREO,  MONITOREADOR, ESPECIALISTA, JEFE, RESPONSABLE, NVL2(ESTADO.DESCRIPCION, ESTADO.DESCRIPCION, 'NO HA INGRESADO' ) ESTADO, PTP.FECHA, PTP.FECHA_APROBACION_JEFE " & _
                        "FROM PLAN_TRABAJO_PERSONA PTP, " & _
                            "(SELECT CODIGO, DESCRIPCION FROM GENERAL WHERE ID = 'ESTADO') ESTADO, " & _
                            "(SELECT PPER.ABREVIATURA, P.PERSONA_MONITOREO,  MONITOREADOR, ESPECIALISTA, JEFE, RESP.RESPONSABLE " & _
                             " FROM POA P,  PPER, " & _
                                         " (SELECT P.PERSONA, P.NOMBRES || ' ' || P.APELLIDO_PATERNO || ' ' || P.APELLIDO_MATERNO as MONITOREADOR, JEFE_INMEDIATO FROM PERSONA P) MONI, " & _
                                         " (SELECT P.PERSONA, P.NOMBRES || ' ' || P.APELLIDO_PATERNO || ' ' || P.APELLIDO_MATERNO as ESPECIALISTA FROM PERSONA P) ESP, " & _
                                         " (SELECT P.PERSONA, P.NOMBRES || ' ' || P.APELLIDO_PATERNO || ' ' || P.APELLIDO_MATERNO as JEFE FROM PERSONA P) JEFEIM, " & _
                                         " (SELECT P.PERSONA, P.NOMBRES || ' ' || P.APELLIDO_PATERNO || ' ' || P.APELLIDO_MATERNO as RESPONSABLE FROM PERSONA P) RESP  " & _
                               "WHERE P.ANHIO_FIRMA_POA = '" & srtCodAnhio & "' AND " & _
                                            "P.PPER = PPER.PPER AND " & _
                                            "P.PERSONA_MONITOREO = MONI.PERSONA AND " & _
                                            "P.PERSONA_ESPECIALISTA = ESP.PERSONA AND " & _
                                            "MONI.JEFE_INMEDIATO = JEFEIM.PERSONA AND " & _
                                            "PPER.RESPONSABLE = RESP.PERSONA " & _
                                "GROUP BY   PPER.ABREVIATURA, P.PERSONA_MONITOREO, MONITOREADOR, ESPECIALISTA, JEFE, PPER.RESPONSABLE, RESP.RESPONSABLE " & _
                                "ORDER BY PPER.ABREVIATURA, MONITOREADOR " & _
                            ") PERS_PPER " & _
                        "WHERE " & _
                                   "PTP.PERSONA_MONITOREO(+) =  PERS_PPER.PERSONA_MONITOREO AND " & _
                                   "PTP.ESTADO = ESTADO.CODIGO (+)  AND " & _
                                   "PTP.PERIODO (+) = '" & strPeriodo & "' " & _
                        "ORDER BY PPER, ESPECIALISTA, MONITOREADOR "
            objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")

            Return objDt

        Catch ex As Exception
            Throw ex
        End Try
    End Function


    Public Function Jefe(ByVal strPersona As String) As DataTable
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String
        Try
            lxc_SQL = "SELECT JEFE_INMEDIATO FROM PERSONA WHERE PERSONA = '" & strPersona & "'"
            objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return objDt
        Catch ex As Exception
            Throw ex
        End Try
    End Function
End Class
